
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 02/11/2013 21:12:53
-- Generated from EDMX file: C:\Users\Alexandre Masson\Documents\Visual Studio 2012\Projects\MusicExplorer\MusicExplorer.Data\MusicExplorer.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [MusicExplorer];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_ARTIST_JN_ARTIST_TRACK]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[JN_ARTIST_TRACK] DROP CONSTRAINT [FK_ARTIST_JN_ARTIST_TRACK];
GO
IF OBJECT_ID(N'[dbo].[FK_ARTIST_WEB_REPOSITORY]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ARTIST] DROP CONSTRAINT [FK_ARTIST_WEB_REPOSITORY];
GO
IF OBJECT_ID(N'[dbo].[FK_GENRE_WEB_REPOSITORY]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[GROUP] DROP CONSTRAINT [FK_GENRE_WEB_REPOSITORY];
GO
IF OBJECT_ID(N'[dbo].[FK_TRACK_JN_ARTIST_TRACK]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[JN_ARTIST_TRACK] DROP CONSTRAINT [FK_TRACK_JN_ARTIST_TRACK];
GO
IF OBJECT_ID(N'[dbo].[FK_TRACK_WEB_REPOSITORY]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TRACK] DROP CONSTRAINT [FK_TRACK_WEB_REPOSITORY];
GO
IF OBJECT_ID(N'[dbo].[FK_TYPE_ARTIST_JN_ARTIST_TRACK]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[JN_ARTIST_TRACK] DROP CONSTRAINT [FK_TYPE_ARTIST_JN_ARTIST_TRACK];
GO
IF OBJECT_ID(N'[dbo].[FK_TYPE_WEB_REPOSITORY_WEB_REPOSITORY]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[WEB_REPOSITORY] DROP CONSTRAINT [FK_TYPE_WEB_REPOSITORY_WEB_REPOSITORY];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[ARTIST]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ARTIST];
GO
IF OBJECT_ID(N'[dbo].[GROUP]', 'U') IS NOT NULL
    DROP TABLE [dbo].[GROUP];
GO
IF OBJECT_ID(N'[dbo].[JN_ARTIST_TRACK]', 'U') IS NOT NULL
    DROP TABLE [dbo].[JN_ARTIST_TRACK];
GO
IF OBJECT_ID(N'[dbo].[REF_TYPE_ARTIST]', 'U') IS NOT NULL
    DROP TABLE [dbo].[REF_TYPE_ARTIST];
GO
IF OBJECT_ID(N'[dbo].[TRACK]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TRACK];
GO
IF OBJECT_ID(N'[dbo].[WEB_REPOSITORY]', 'U') IS NOT NULL
    DROP TABLE [dbo].[WEB_REPOSITORY];
GO

IF OBJECT_ID(N'[dbo].[REF_TYPE_WEB_REPOSITORY]', 'U') IS NOT NULL
    DROP TABLE [dbo].[REF_TYPE_WEB_REPOSITORY];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'ARTIST'
CREATE TABLE [dbo].[ARTIST] (
    [web_repo_id] uniqueidentifier  NOT NULL,
    [artist_key] nvarchar(100)  NOT NULL,
    [artist_name] nvarchar(100)  NOT NULL,
	[artist_visibility] bit NOT NULL DEFAULT 1,
	[artist_order] int NOT NULL DEFAULT 0,
	[is_registered] bit NOT NULL DEFAULT 0
);
GO

-- Creating table 'GROUP'
CREATE TABLE [dbo].[GROUP] (
    [web_repo_id] uniqueidentifier  NOT NULL,
    [group_key] nvarchar(100)  NOT NULL,
    [group_name] nvarchar(50)  NOT NULL,
	[group_visibility] bit NOT NULL DEFAULT 1,
	[group_order] int NOT NULL DEFAULT 0
);
GO

-- Creating table 'JN_ARTIST_TRACK'
CREATE TABLE [dbo].[JN_ARTIST_TRACK] (
    [web_repo_id] uniqueidentifier  NOT NULL,
    [artist_key] nvarchar(100)  NOT NULL,
    [track_key] nvarchar(100)  NOT NULL,
    [type_artist_id] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'REF_TYPE_ARTIST'
CREATE TABLE [dbo].[REF_TYPE_ARTIST] (
    [type_artist_id] uniqueidentifier  NOT NULL,
    [type_artist_libelle] nvarchar(100)  NOT NULL
);
GO

-- Creating table 'TRACK'
CREATE TABLE [dbo].[TRACK] (
    [web_repo_id] uniqueidentifier  NOT NULL,
    [group_key] nvarchar(100)  NULL,
    [track_key] nvarchar(100)  NOT NULL,
    [track_name] nvarchar(100)  NOT NULL,
    [track_mp3_url] nvarchar(500)  NULL,
    [track_img_url] nvarchar(500)  NULL,
    [track_publish_date] datetime  NOT NULL,
	[track_is_read] bit NOT NULL DEFAULT 0,
	[track_is_favorite] bit NOT NULL DEFAULT 0,
	[track_duration] int NULL
);
GO

-- Creating table 'WEB_REPOSITORY'
CREATE TABLE [dbo].[WEB_REPOSITORY] (
    [web_repo_id] uniqueidentifier  NOT NULL,
    [web_repo_key] nvarchar(100)  NOT NULL,
	[web_repo_name] nvarchar(50)  NOT NULL,
	[type_web_repo_id] uniqueidentifier NULL,
	[web_repo_visibility] bit NOT NULL DEFAULT 1,
	[web_repo_order] int NOT NULL DEFAULT 0
);
GO

-- Creating table 'REF_WEB_REPOSITORY'
CREATE TABLE [dbo].[REF_TYPE_WEB_REPOSITORY] (
    [type_web_repo_id] uniqueidentifier  NOT NULL,
	[type_web_repo_key] nvarchar(50) NOT NULL,
    [type_web_repo_libelle] nvarchar(50)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [web_repo_id], [artist_key] in table 'ARTIST'
ALTER TABLE [dbo].[ARTIST]
ADD CONSTRAINT [PK_ARTIST]
    PRIMARY KEY CLUSTERED ([web_repo_id], [artist_key] ASC);
GO

-- Creating primary key on [web_repo_id], [group_key] in table 'GROUP'
ALTER TABLE [dbo].[GROUP]
ADD CONSTRAINT [PK_GROUP]
    PRIMARY KEY CLUSTERED ([web_repo_id], [group_key] ASC);
GO

-- Creating primary key on [web_repo_id], [artist_key], [track_key] in table 'JN_ARTIST_TRACK'
ALTER TABLE [dbo].[JN_ARTIST_TRACK]
ADD CONSTRAINT [PK_JN_ARTIST_TRACK]
    PRIMARY KEY CLUSTERED ([web_repo_id], [artist_key], [track_key] ASC);
GO

-- Creating primary key on [type_artist_id] in table 'REF_TYPE_ARTIST'
ALTER TABLE [dbo].[REF_TYPE_ARTIST]
ADD CONSTRAINT [PK_REF_TYPE_ARTIST]
    PRIMARY KEY CLUSTERED ([type_artist_id] ASC);
GO

-- Creating primary key on [type_artist_id] in table 'REF_TYPE_ARTIST'
ALTER TABLE [dbo].[REF_TYPE_WEB_REPOSITORY]
ADD CONSTRAINT [PK_REF_TYPE_WEB_REPOSITORY]
    PRIMARY KEY CLUSTERED ([type_web_repo_id] ASC);
GO

-- Creating primary key on [web_repo_id], [track_key] in table 'TRACK'
ALTER TABLE [dbo].[TRACK]
ADD CONSTRAINT [PK_TRACK]
    PRIMARY KEY CLUSTERED ([web_repo_id], [track_key] ASC);
GO

-- Creating primary key on [web_repo_id] in table 'WEB_REPOSITORY'
ALTER TABLE [dbo].[WEB_REPOSITORY]
ADD CONSTRAINT [PK_WEB_REPOSITORY]
    PRIMARY KEY CLUSTERED ([web_repo_id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [web_repo_id], [artist_key] in table 'JN_ARTIST_TRACK'
ALTER TABLE [dbo].[JN_ARTIST_TRACK]
ADD CONSTRAINT [FK_ARTIST_JN_ARTIST_TRACK]
    FOREIGN KEY ([web_repo_id], [artist_key])
    REFERENCES [dbo].[ARTIST]
        ([web_repo_id], [artist_key])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [web_repo_id] in table 'ARTIST'
ALTER TABLE [dbo].[ARTIST]
ADD CONSTRAINT [FK_ARTIST_WEB_REPOSITORY]
    FOREIGN KEY ([web_repo_id])
    REFERENCES [dbo].[WEB_REPOSITORY]
        ([web_repo_id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [web_repo_id] in table 'GROUP'
ALTER TABLE [dbo].[GROUP]
ADD CONSTRAINT [FK_GENRE_WEB_REPOSITORY]
    FOREIGN KEY ([web_repo_id])
    REFERENCES [dbo].[WEB_REPOSITORY]
        ([web_repo_id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [web_repo_id], [group_key] in table 'TRACK'
ALTER TABLE [dbo].[TRACK]
ADD CONSTRAINT [FK_TRACK_WEB_REPOSITORY]
    FOREIGN KEY ([web_repo_id], [group_key])
    REFERENCES [dbo].[GROUP]
        ([web_repo_id], [group_key])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TRACK_WEB_REPOSITORY'
CREATE INDEX [IX_FK_TRACK_WEB_REPOSITORY]
ON [dbo].[TRACK]
    ([web_repo_id], [group_key]);
GO

-- Creating foreign key on [web_repo_id], [track_key] in table 'JN_ARTIST_TRACK'
ALTER TABLE [dbo].[JN_ARTIST_TRACK]
ADD CONSTRAINT [FK_TRACK_JN_ARTIST_TRACK]
    FOREIGN KEY ([web_repo_id], [track_key])
    REFERENCES [dbo].[TRACK]
        ([web_repo_id], [track_key])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TRACK_JN_ARTIST_TRACK'
CREATE INDEX [IX_FK_TRACK_JN_ARTIST_TRACK]
ON [dbo].[JN_ARTIST_TRACK]
    ([web_repo_id], [track_key]);
GO

-- Creating foreign key on [type_artist_id] in table 'JN_ARTIST_TRACK'
ALTER TABLE [dbo].[JN_ARTIST_TRACK]
ADD CONSTRAINT [FK_TYPE_ARTIST_JN_ARTIST_TRACK]
    FOREIGN KEY ([type_artist_id])
    REFERENCES [dbo].[REF_TYPE_ARTIST]
        ([type_artist_id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating foreign key on [type_web_repo_id] in table 'WEB_REPOSITORY'
ALTER TABLE [dbo].[WEB_REPOSITORY]
ADD CONSTRAINT [FK_TYPE_WEB_REPOSITORY_WEB_REPOSITORY]
    FOREIGN KEY ([type_web_repo_id])
    REFERENCES [dbo].[REF_TYPE_WEB_REPOSITORY]
        ([type_web_repo_id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TYPE_ARTIST_JN_ARTIST_TRACK'
CREATE INDEX [IX_FK_TYPE_ARTIST_JN_ARTIST_TRACK]
ON [dbo].[JN_ARTIST_TRACK]
    ([type_artist_id]);
GO

-- Creating non-clustered index for FOREIGN KEY 'FK_TYPE_WEB_REPOSITORY_WEB_REPOSITORY'
CREATE INDEX [IX_FK_TYPE_WEB_REPOSITORY_WEB_REPOSITORY]
ON [dbo].[WEB_REPOSITORY]
    (type_web_repo_id);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------